 with last_month_network_data as(
  select
    customer_code,                                                       --客户编码
    max(customer_name) as customer_name,                                 --客户名字
    concat_ws(',',collect_set(ordersource_code)) as ordersource_code,    --平台ID
    ordersource_name,                                                    --平台名字
    max(customer_type_id) as customer_type_id,
    max(customer_type_name) as customer_type_name,
    max(customer_category) as customer_category,
    max(customer_category_name) as customer_category_name,
    max(customer_attribute_id) as customer_attribute_id,
    max(customer_attribute_name) as customer_attribute_name,
    max(subordinate_agent_name) as subordinate_agent_name,
    max(subordinate_agent_code) as subordinate_agent_code,
    max(first_franchisee_id) as first_franchisee_id,
    max(first_franchisee_code) as first_franchisee_code,
    max(first_franchisee_name) as first_franchisee_name,
    max(customer_belong_network_id) as customer_belong_network_id,
    max(customer_belong_network_name) as customer_belong_network_name,
    max(customer_province_id) as customer_province_id,
    max(customer_province_desc) as customer_province_desc,
    max(customer_city_id) as customer_city_id,
    max(customer_city_desc) as customer_city_desc,
    max(customer_area_id) as customer_area_id,
    max(customer_area_desc) as customer_area_desc,
    sum(nvl(day_customer_sum,0)) as month_customer_waybill_sum,   --客户运单总量
  min(customer_signed_time) as customer_signed_time
  from jms_dm.dm_customer_base_detail_dt
  where dt >=trunc(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}')),'MM')
  and dt< trunc('{{ execution_date | cst_ds }}','MM')
  and day_customer_sum>0
  group by customer_code,ordersource_name
  ),
  --今日数据
  month_customer as(
  select
      nvl(a.customer_code,b.customer_code) as customer_code,                                                                                               --客户编码
      nvl(a.customer_name,b.customer_name) as customer_name,                                                                                               --客户名字
      case when  nvl(a.month_customer_waybill_sum,0) /day('{{ execution_date | cst_ds }}') >5000.0 then 'A'
            when nvl(a.month_customer_waybill_sum,0) /day('{{ execution_date | cst_ds }}') >1000.0 then 'B'
            when nvl(a.month_customer_waybill_sum,0) /day('{{ execution_date | cst_ds }}') >200.0 then 'C'
            when nvl(a.month_customer_waybill_sum,0) /day('{{ execution_date | cst_ds }}') >50.0 then 'D'
            else 'E' end  as customer_level,                                                                        --客户等级
      case when  nvl(b.month_customer_waybill_sum,0) /day(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}'))) >5000.0 then 'A'
           when nvl(b.month_customer_waybill_sum,0) /day(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}'))) >1000.0 then 'B'
           when nvl(b.month_customer_waybill_sum,0) /day(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}'))) >200.0 then 'C'
           when nvl(b.month_customer_waybill_sum,0) /day(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}'))) >50.0 then 'D'
           else 'E' end  as last_month_customer_level,                                                                        --客户等级
      nvl(a.ordersource_code,b.ordersource_code) as ordersource_code,                                               --平台ID
      nvl(a.ordersource_name,b.ordersource_name) as ordersource_name,                                               --平台名字
      nvl(a.customer_type_id,b.customer_type_id) as customer_type_id  ,                                             -- 客户类型
      nvl(a.customer_type_name,b.customer_type_name) as customer_type_name,                                         --客户类型名字
      nvl(a.customer_category,b.customer_category) as customer_category,                                            --客户类别
      nvl(a.customer_category_name,b.customer_category_name) as customer_category_name,
      nvl(a.customer_attribute_id,b.customer_attribute_id) as customer_attribute_id ,                                --客户属性ID
      nvl(a.customer_attribute_name,b.customer_attribute_name) as customer_attribute_name,                           --客户属性名字
      nvl(a.subordinate_agent_name,b.subordinate_agent_name) as subordinate_agent_name,                              --所属代理区
      nvl(a.subordinate_agent_code,b.subordinate_agent_code) as subordinate_agent_code,                              --所属代理区code
      nvl(a.first_franchisee_id,b.first_franchisee_id) as first_franchisee_id    ,                                   -- 一级加盟商id',
      nvl(a.first_franchisee_code,b.first_franchisee_code) as first_franchisee_code,                                 -- '一级加盟商code',
      nvl(a.first_franchisee_name,b.first_franchisee_name) as first_franchisee_name  ,                               -- 一级加盟商',
      nvl(a.customer_belong_network_id,b.customer_belong_network_id) as customer_belong_network_id ,                 -- 客户所属网点ID
      nvl(a.customer_belong_network_name,b.customer_belong_network_name) as customer_belong_network_name ,           --  客户所属网点名字
      nvl(a.customer_province_id,b.customer_province_id) as customer_province_id   ,                                 -- 客户所属省份id
      nvl(a.customer_province_desc,b.customer_province_desc) as customer_province_desc ,                             --客户所属省份名字
      nvl(a.customer_city_id,b.customer_city_id) as customer_city_id     ,                                           -- 客户所属城市id
      nvl(a.customer_city_desc,b.customer_city_desc) as customer_city_desc,                                          --  客户所属城市名字',
      nvl(a.customer_area_id,b.customer_area_id) as customer_area_id   ,                                             --  客户所属区域id
      nvl(a.customer_area_desc,b.customer_area_desc) as customer_area_desc ,                                         -- 客户所属区县的名字',
      nvl(a.month_customer_waybill_sum,0) as month_customer_waybill_sum ,                                            --本月运单总量
      nvl(b.month_customer_waybill_sum,0) as last_month_customer_waybill_sum ,                                       --上月月运单总量
      nvl(a.customer_signed_time,b.customer_signed_time) as customer_signed_time,                                    --客户创建时间
     if(a.customer_signed_time>=trunc('{{ execution_date | cst_ds }}','MM') and a.customer_signed_time<='{{ execution_date | cst_ds }}' and nvl(a.month_customer_waybill_sum,0)>0 ,1,0)as is_new,    --是否为新增
     if(a.customer_signed_time>=trunc('{{ execution_date | cst_ds }}','MM') and a.customer_signed_time<='{{ execution_date | cst_ds }}' and nvl(a.month_customer_waybill_sum,0)>0 ,nvl(a.month_customer_waybill_sum,0) ,0)as new_customer_waybill_sum,  --是否为新增
     case when a.ordersource_name is null  and b.ordersource_name is not null then 1
           else 0 end as is_loss,                                                                                    --是否流失
    case when  a.ordersource_name is null  and b.ordersource_name is not null then b.month_customer_waybill_sum
          else 0 end as loss_waybill_sum,                                                                           --流失总运单量
    case when a.customer_signed_time<trunc('{{ execution_date | cst_ds }}','MM') and  a.ordersource_name is not null  and b.ordersource_name is null then 1
         else 0 end as is_retrieve,                                                                                 --是否挽回
    case when a.customer_signed_time<trunc('{{ execution_date | cst_ds }}','MM') and  a.ordersource_name is not null  and b.ordersource_name is null then nvl(a.month_customer_waybill_sum,0)
          else 0 end as retrieve_waybill_sum                                                                        --挽回总运单量
  from (
  select
      customer_code,                                                            --客户编码
      max(customer_name) as customer_name,                                      --客户名字
     concat_ws(',',collect_set(ordersource_code)) as ordersource_code,                                                      --平台ID
     ordersource_name,                                                      --平台名字
     max(customer_type_id) as customer_type_id  ,                                                    -- 客户类型
     max(customer_type_name) as customer_type_name,                                                   --客户类型名字
     max(customer_category) as customer_category,                                                 --客户类别
     max(customer_category_name) as customer_category_name,
     max(customer_attribute_id) as customer_attribute_id ,                                                   --客户属性ID
     max(customer_attribute_name) as customer_attribute_name,
     max(subordinate_agent_code) as subordinate_agent_code,                                       --所属代理区code
max(subordinate_agent_name) as subordinate_agent_name,                                       --所属代理区
     max(first_franchisee_id) as first_franchisee_id    ,                                       -- 一级加盟商id',
     max(first_franchisee_code) as first_franchisee_code,                                        -- '一级加盟商code',
     max(first_franchisee_name) as first_franchisee_name  ,                                                    -- 一级加盟商',
     max(customer_belong_network_id) as customer_belong_network_id ,                                                 -- 客户所属网点ID
     max(customer_belong_network_name) as customer_belong_network_name ,                                               --  客户所属网点名字
     max(customer_province_id) as customer_province_id   ,                                                     -- 客户所属省份id
max(customer_province_desc) as customer_province_desc ,                                                     --客户所属省份名字
     max(customer_city_id) as customer_city_id     ,                                                       -- 客户所属城市id
max(customer_city_desc) as customer_city_desc,                                                          --  客户所属城市名字',
     max(customer_area_id) as customer_area_id   ,                                                         --  客户所属区域id
max(customer_area_desc) as customer_area_desc ,                                                         -- 客户所属区县的名字',
sum(day_customer_sum) as month_customer_waybill_sum ,                                                           --运单量
min(customer_signed_time) as customer_signed_time                                                        --客户创建时间
  from  jms_dm.dm_customer_base_detail_dt
  where dt >= trunc('{{ execution_date | cst_ds }}','MM')
  and dt <='{{ execution_date | cst_ds }}'
  and day_customer_sum>0
  group by customer_code,ordersource_name
  )   a
  full join  last_month_network_data b
  on a.customer_code=b.customer_code
  and a.ordersource_name=b.ordersource_name
  where (a.ordersource_name is not null or b.ordersource_name is not null)
  ),

 network_customer as(
 select
             'network' as org_level,
             'Month' as date_type,
             today.customer_level,           --客户等级
             today.ordersource_code,         --客户平台ID
             today.ordersource_name,  --平台名字
             today.subordinate_agent_code,  --代理区code
             today.subordinate_agent_name,--代理区名字
             today.first_franchisee_code,  --加盟商code
             today.first_franchisee_name,  --加盟商名字
             today.customer_belong_network_id,  --客户所属网点ID
             today.customer_belong_network_name,  --客户所属网点名字
             today.customer_cnt,    --发件客数
             today_sum.today_customer_cnt,
             round(today.customer_cnt/today_sum.today_customer_cnt,4) as customer_cnt_rate,
             yesterday.last_month_customer_cnt,
             today.month_customer_waybill_sum,--发件客户运单总量
             today_sum.today_month_customer_waybill_sum,
             round(today.month_customer_waybill_sum/today_sum.today_month_customer_waybill_sum,4) as customer_waybill_rate,
             round(today.customer_cnt/yesterday.last_month_customer_cnt-1,4) as customer_up_rate,  --客户提升率
             today.new_cnt,      --新增客户数
             today.new_customer_waybill_sum,  --新增客户运单总量
             round(today.new_cnt/yesterday.last_month_customer_cnt,4) as new_customer_rate,  --新增客户率
             today.loss_cnt,   --流失客户数
             today.loss_waybill_sum,  --流失客户运单总量
             round(today.loss_cnt/yesterday.last_month_customer_cnt,4) as loss_customer_rate,  --流失客户率
             today.retrieve_cnt,  --挽回客户数
             today.retrieve_waybill_sum,   --挽回客户运单总量
             round(today.retrieve_cnt/yesterday.last_month_customer_cnt,4) as retrieve_customer_rate,  --挽回客户率
             '{{ execution_date | cst_ds }}' as dt
from (
             select
             customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             customer_belong_network_id,  --客户所属网点ID
             max(subordinate_agent_code) as subordinate_agent_code,
             ordersource_name,  --平台名字
             max(subordinate_agent_name) as subordinate_agent_name,
             max(first_franchisee_code) as first_franchisee_code,
             max(first_franchisee_name) as first_franchisee_name,
             max(customer_belong_network_name) as customer_belong_network_name,  --客户所属网点名字
             sum(if(a.month_customer_waybill_sum>0,1,0)) as customer_cnt,    --发件客数
             sum(nvl(month_customer_waybill_sum,0))  as month_customer_waybill_sum,  --发件客户运单总量
             sum(is_new) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             sum(is_loss)  as  loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             sum(is_retrieve)  as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum   --挽回客户运单总量
             from month_customer a
             --where a.month_customer_waybill_sum>0
             group by customer_level,ordersource_name,customer_belong_network_id
      )today
  left join (
           select
           customer_belong_network_id,       --客户所属网点ID
           max(customer_belong_network_name) as customer_belong_network_name,   --客户所属网点名字
           ordersource_name,
           last_month_customer_level,
           count( distinct customer_code) as last_month_customer_cnt
           from month_customer
           where last_month_customer_waybill_sum>0
 --          from jms_dm.dm_customer_base_detail_dt
 --          where dt >=trunc(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}')),'MM')
 --         and dt< trunc('{{ execution_date | cst_ds }}','MM')
 --          and day_customer_sum>0
           group by customer_belong_network_id,ordersource_name,last_month_customer_level
        )yesterday
on today.customer_belong_network_id=yesterday.customer_belong_network_id
and today.ordersource_name=yesterday.ordersource_name
and today.customer_level=yesterday.last_month_customer_level
join (
  select
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             customer_belong_network_id,  --客户所属网点ID
             max(customer_belong_network_name) as customer_belong_network_name,  --客户所属网点名字
             count(distinct customer_code) as today_customer_cnt,    --发件客数
             sum(month_customer_waybill_sum) as today_month_customer_waybill_sum  --今日总的运单量
             from month_customer a
             where a.month_customer_waybill_sum>0
             group by ordersource_name,customer_belong_network_id ) today_sum
on today.customer_belong_network_id=today_sum.customer_belong_network_id
and today.ordersource_name=today_sum.ordersource_name
 ),
 network_customer_sum as (
         select
             'network' as org_level,
             'Month' as date_type,
             '汇总' as customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,--代理区名字
             first_franchisee_code,  --加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --加盟商名字
             customer_belong_network_id,  --客户所属网点ID
             max(customer_belong_network_name) as customer_belong_network_name,  --客户所属网点名字
             sum(customer_cnt) as customer_cnt,    --发件客数
             max(today_customer_cnt) as today_customer_cnt,
             1 as customer_cnt_rate,
             sum(last_month_customer_cnt) as last_month_customer_cnt,
             sum(month_customer_waybill_sum) as month_customer_waybill_sum,--发件客户运单总量
             max(today_month_customer_waybill_sum) as today_month_customer_waybill_sum,
             1 as customer_waybill_rate,
             round(sum(customer_cnt)/sum(last_month_customer_cnt)-1,4) as customer_up_rate,  --客户提升率
             sum(new_cnt)as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             round(sum(new_cnt)/sum(last_month_customer_cnt),4) as new_customer_rate,  --新增客户率
             sum(loss_cnt) as loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             round(sum(loss_cnt)/sum(last_month_customer_cnt),4) as loss_customer_rate,  --流失客户率
             sum(retrieve_cnt) as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum,   --挽回客户运单总量
             round(sum(retrieve_cnt)/sum(last_month_customer_cnt),4) as retrieve_customer_rate  --挽回客户率
           from  network_customer
           group by ordersource_name,subordinate_agent_code,first_franchisee_code,customer_belong_network_id
 ),
 --加盟商
 fran_customer as(
          select
             'fran' as org_level,
             'Month' as date_type,
             today.customer_level,           --客户等级
             today.ordersource_code,         --客户平台ID
             today.ordersource_name,  --平台名字
             today.subordinate_agent_code,  --代理区code
             today.subordinate_agent_name,--代理区名字
             today.first_franchisee_code,  --加盟商code
             today.first_franchisee_name,  --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as customer_belong_network_name,  --客户所属网点名字
             today.customer_cnt,    --发件客数
             today_sum.today_customer_cnt,
             round(today.customer_cnt/today_sum.today_customer_cnt,4) as customer_cnt_rate,
             yesterday.last_month_customer_cnt,
             today.month_customer_waybill_sum,--发件客户运单总量
             today_sum.today_month_customer_waybill_sum,
             round(today.month_customer_waybill_sum/today_sum.today_month_customer_waybill_sum,4) as customer_waybill_rate,
             round(today.customer_cnt/yesterday.last_month_customer_cnt-1,4) as customer_up_rate,  --客户提升率
             today.new_cnt,      --新增客户数
             today.new_customer_waybill_sum,  --新增客户运单总量
             round(today.new_cnt/yesterday.last_month_customer_cnt,4) as new_customer_rate,  --新增客户率
             today.loss_cnt,   --流失客户数
             today.loss_waybill_sum,  --流失客户运单总量
             round(today.loss_cnt/yesterday.last_month_customer_cnt,4) as loss_customer_rate,  --流失客户率
             today.retrieve_cnt,  --挽回客户数
             today.retrieve_waybill_sum,   --挽回客户运单总量
             round(today.retrieve_cnt/yesterday.last_month_customer_cnt,4) as retrieve_customer_rate,  --挽回客户率
            '{{ execution_date | cst_ds }}' as dt
from (
             select
             customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code))  as ordersource_code,         --客户平台ID
             max(subordinate_agent_code) as subordinate_agent_code,   --代理区code
             ordersource_name,  --平台名字
              max(subordinate_agent_name) as subordinate_agent_name,  --代理区名字
             first_franchisee_code,                               --客户所加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --客户所属加盟商名字
             --'network' as level_1,
             sum( if(month_customer_waybill_sum>0,1,0)) as customer_cnt,    --发件客数
             sum(nvl(month_customer_waybill_sum,0))  as month_customer_waybill_sum,  --发件客户运单总量
             sum(is_new) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             sum(is_loss)  as  loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             sum(is_retrieve)  as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum   --挽回客户运单总量
             from month_customer
             --where month_customer.month_customer_waybill_sum>0
             group by customer_level,ordersource_name,first_franchisee_code
      )today
  left join (
           select
           first_franchisee_code,       --客户所属加盟商code
           max(first_franchisee_name) as first_franchisee_name,   --客户所属加盟商名字
           ordersource_name,
           last_month_customer_level,
           count( distinct customer_code) as last_month_customer_cnt
           from month_customer
           where last_month_customer_waybill_sum>0
--           from jms_dm.dm_customer_base_detail_dt
--           where dt >=trunc(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}')),'MM')
--           and dt< trunc('{{ execution_date | cst_ds }}','MM')
--           and day_customer_sum>0
           group by first_franchisee_code,ordersource_name,last_month_customer_level
        )yesterday
on today.first_franchisee_code=yesterday.first_franchisee_code
and today.ordersource_name=yesterday.ordersource_name
and today.customer_level=yesterday.last_month_customer_level
join (
  select
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             first_franchisee_code,  --客户所加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --客户所属网点名字
             count(distinct customer_code) as today_customer_cnt,    --发件客户个数
             sum(month_customer_waybill_sum) as today_month_customer_waybill_sum  --今日总的运单量
             from month_customer a
             where a.month_customer_waybill_sum>0
             group by ordersource_name,subordinate_agent_code,first_franchisee_code
             ) today_sum
on today.first_franchisee_code=today_sum.first_franchisee_code
and today.ordersource_name=today_sum.ordersource_name
 ),
fran_customer_sum as (
 select
             'fran' as org_level,
             'Month' as date_type,
             '汇总' as customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,--代理区名字
             first_franchisee_code,  --加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as  customer_belong_network_name,  --客户所属网点名字
             sum(customer_cnt) as customer_cnt,    --发件客数
             max(today_customer_cnt) as today_customer_cnt,
             1 as customer_cnt_rate,
             sum(last_month_customer_cnt) as last_month_customer_cnt,
             sum(month_customer_waybill_sum) as month_customer_waybill_sum,--发件客户运单总量
             max(today_month_customer_waybill_sum) as today_month_customer_waybill_sum,
             1 as customer_waybill_rate,
             round(sum(customer_cnt)/sum(last_month_customer_cnt)-1,4) as customer_up_rate,  --客户提升率
             sum(new_cnt)as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             round(sum(new_cnt)/sum(last_month_customer_cnt),4) as new_customer_rate,  --新增客户率
             sum(loss_cnt) as loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             round(sum(loss_cnt)/sum(last_month_customer_cnt),4) as loss_customer_rate,  --流失客户率
             sum(retrieve_cnt) as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum,   --挽回客户运单总量
             round(sum(retrieve_cnt)/sum(last_month_customer_cnt),4) as retrieve_customer_rate  --挽回客户率
          from  fran_customer
          group by ordersource_name,subordinate_agent_code,first_franchisee_code
 ),
--代理区
   agent_customer as(
          select
             'agent' as org_level,
             'Month' as date_type,
             today.customer_level,           --客户等级
             today.ordersource_code,         --客户平台ID
             today.ordersource_name,  --平台名字
             today.subordinate_agent_code,  --客户所属代理区code
             today.subordinate_agent_name,  --客户所属代理区名字
             null as first_franchisee_code,  --加盟商code
             null as first_franchisee_name,  --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as customer_belong_network_name,  --客户所属网点名字
             today.customer_cnt,    --发件客数
             today_sum.today_customer_cnt,
             round(today.customer_cnt/today_sum.today_customer_cnt) as customer_cnt_rate,
             yesterday.last_month_customer_cnt,
             today.month_customer_waybill_sum,--发件客户运单总量
             today_sum.today_month_customer_waybill_sum,
            round(today.month_customer_waybill_sum/today_sum.today_month_customer_waybill_sum) as customer_waybill_rate,
             round(today.customer_cnt/yesterday.last_month_customer_cnt-1,4) as customer_up_rate,  --客户提升率
             today.new_cnt,      --新增客户数
             today.new_customer_waybill_sum,  --新增客户运单总量
             round(today.new_cnt/yesterday.last_month_customer_cnt,4) as new_customer_rate,  --新增客户率
             today.loss_cnt,   --流失客户数
             today.loss_waybill_sum,  --流失客户运单总量
             round(today.loss_cnt/yesterday.last_month_customer_cnt,4) as loss_customer_rate,  --新增客户率
             today.retrieve_cnt,  --挽回客户数
             today.retrieve_waybill_sum,   --挽回客户运单总量
             round(today.retrieve_cnt/yesterday.last_month_customer_cnt,4) as retrieve_customer_rate,  --新增客户率
            '{{ execution_date | cst_ds }}' as dt
from (
             select
             customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             subordinate_agent_code,  --客户所加盟商code
             max(subordinate_agent_name) as subordinate_agent_name,  --客户所属网点名字
             --'network' as level_1,
             ordersource_name,  --平台名字
             sum( if(a.month_customer_waybill_sum>0,1,0)) as customer_cnt,    --发件客数
             sum(nvl(month_customer_waybill_sum,0))  as month_customer_waybill_sum,  --发件客户运单总量
             sum(is_new) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             sum(is_loss)  as  loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             sum(is_retrieve)  as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum   --挽回客户运单总量
             from month_customer a
            -- where a.month_customer_waybill_sum>0
             group by customer_level,ordersource_name,subordinate_agent_code
      )today
  left join (
           select
           subordinate_agent_code,       --客户所属代理区code
           max(subordinate_agent_name) as subordinate_agent_name,   --客户所属代理区名字
           ordersource_name,
           last_month_customer_level,
           count( distinct customer_code) as last_month_customer_cnt
           from month_customer
            where last_month_customer_waybill_sum>0
--           from jms_dm.dm_customer_base_detail_dt
--           where dt >=trunc(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}')),'MM')
--            and dt< trunc('{{ execution_date | cst_ds }}','MM')
--           and day_customer_sum>0
           group by subordinate_agent_code,ordersource_name,last_month_customer_level
        )yesterday
on today.subordinate_agent_code=yesterday.subordinate_agent_code
and today.ordersource_name=yesterday.ordersource_name
and today.customer_level=yesterday.last_month_customer_level
join (
  select
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --客户代理区code
             max(subordinate_agent_name) as subordinate_agent_name,  --客户代理区名字
             count(distinct customer_code) as today_customer_cnt,    --发件客户个数
             sum(month_customer_waybill_sum) as today_month_customer_waybill_sum  --今日总的运单量
             from month_customer a
             where a.month_customer_waybill_sum>0
             group by ordersource_name,subordinate_agent_code
            ) today_sum
on today.subordinate_agent_code=today_sum.subordinate_agent_code
and today.ordersource_name=today_sum.ordersource_name
 ),
 agent_customer_sum as(
 select
             'agent' as org_level,
             'Month' as date_type,
             '汇总' as customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,--代理区名字
             null as first_franchisee_code,  --加盟商code
             null as  first_franchisee_name,  --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as  customer_belong_network_name,  --客户所属网点名字
             sum(customer_cnt) as customer_cnt,    --发件客数
             max(today_customer_cnt) as today_customer_cnt,
             1 as customer_cnt_rate,
             sum(last_month_customer_cnt) as last_month_customer_cnt,
             sum(month_customer_waybill_sum) as month_customer_waybill_sum,--发件客户运单总量
             max(today_month_customer_waybill_sum) as today_month_customer_waybill_sum,
             1 as customer_waybill_rate,
             round(sum(customer_cnt)/sum(last_month_customer_cnt)-1,4) as customer_up_rate,  --客户提升率
             sum(new_cnt)as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             round(sum(new_cnt)/sum(last_month_customer_cnt),4) as new_customer_rate,  --新增客户率
             sum(loss_cnt) as loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             round(sum(loss_cnt)/sum(last_month_customer_cnt),4) as loss_customer_rate,  --流失客户率
             sum(retrieve_cnt) as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum,   --挽回客户运单总量
             round(sum(retrieve_cnt)/sum(last_month_customer_cnt),4) as retrieve_customer_rate  --挽回客户率
        from  agent_customer
        group by ordersource_name,subordinate_agent_code
 )

insert overwrite table jms_dm.dm_customer_changes_month_dt partition(dt,org_level)
 select
              date_type,
              customer_level,           --客户等级
              ordersource_code,         --客户平台ID
              ordersource_name,  --平台名字
              subordinate_agent_code,
              subordinate_agent_name,
              first_franchisee_code,
              first_franchisee_name,
              customer_belong_network_id,  --客户所属网点ID
              customer_belong_network_name,  --客户所属网点名字
              customer_cnt,    --发件客数
              today_customer_cnt,
              customer_cnt_rate,
              last_month_customer_cnt,
              month_customer_waybill_sum,--发件客户运单总量
              today_month_customer_waybill_sum,
              customer_waybill_rate,
              customer_up_rate,  --客户提升率
              new_cnt,      --新增客户数
              new_customer_waybill_sum,  --新增客户运单总量
              new_customer_rate,  --新增客户率
              loss_cnt,   --流失客户数
              loss_waybill_sum,  --流失客户运单总量
              loss_customer_rate,   --流失率
              retrieve_cnt,  --挽回客户数
              retrieve_waybill_sum,   --挽回客户运单总量
              retrieve_customer_rate,  --挽回率
              '{{ execution_date | cst_ds }}' as dt2,
               org_level as org_level2,
              '{{ execution_date | cst_ds }}' as dt,
               org_level
 from (
 select
             org_level,
              date_type,
              customer_level,           --客户等级
              ordersource_code,         --客户平台ID
              ordersource_name,  --平台名字
              subordinate_agent_code,
              subordinate_agent_name,
              first_franchisee_code,
              first_franchisee_name,
              customer_belong_network_id,  --客户所属网点ID
              customer_belong_network_name,  --客户所属网点名字
              customer_cnt,    --发件客数
              today_customer_cnt,
              customer_cnt_rate,
              last_month_customer_cnt,
              month_customer_waybill_sum,--发件客户运单总量
              today_month_customer_waybill_sum,
              customer_waybill_rate,
              customer_up_rate,  --客户提升率
              new_cnt,      --新增客户数
              new_customer_waybill_sum,  --新增客户运单总量
              new_customer_rate,  --新增客户率
              loss_cnt,   --流失客户数
              loss_waybill_sum,  --流失客户运单总量
              loss_customer_rate,  --流失率
              retrieve_cnt,  --挽回客户数
              retrieve_waybill_sum,   --挽回客户运单总量
              retrieve_customer_rate       --挽回率
 from network_customer
 union all
 select
             org_level,
              date_type,
              customer_level,           --客户等级
              ordersource_code,         --客户平台ID
              ordersource_name,  --平台名字
              subordinate_agent_code,
              subordinate_agent_name,
              first_franchisee_code,
              first_franchisee_name,
              customer_belong_network_id,  --客户所属网点ID
              customer_belong_network_name,  --客户所属网点名字
              customer_cnt,    --发件客数
              today_customer_cnt,
              customer_cnt_rate,
              last_month_customer_cnt,
              month_customer_waybill_sum,--发件客户运单总量
              today_month_customer_waybill_sum,
              customer_waybill_rate,
              customer_up_rate,  --客户提升率
              new_cnt,      --新增客户数
              new_customer_waybill_sum,  --新增客户运单总量
              new_customer_rate,  --新增客户率
              loss_cnt,   --流失客户数
              loss_waybill_sum,  --流失客户运单总量
              loss_customer_rate,  --流失率
              retrieve_cnt,  --挽回客户数
              retrieve_waybill_sum,   --挽回客户运单总量
              retrieve_customer_rate       --挽回率
 from network_customer_sum
 union all
 select
                         org_level,
                          date_type,
                          customer_level,           --客户等级
                          ordersource_code,         --客户平台ID
                          ordersource_name,  --平台名字
                          subordinate_agent_code,
                          subordinate_agent_name,
                          first_franchisee_code,
                          first_franchisee_name,
                          customer_belong_network_id,  --客户所属网点ID
                          customer_belong_network_name,  --客户所属网点名字
                          customer_cnt,    --发件客数
                          today_customer_cnt,
                          customer_cnt_rate,
                         last_month_customer_cnt,
                          month_customer_waybill_sum,--发件客户运单总量
                          today_month_customer_waybill_sum,
                          customer_waybill_rate,
                          customer_up_rate,  --客户提升率
                          new_cnt,      --新增客户数
                          new_customer_waybill_sum,  --新增客户运单总量
                          new_customer_rate,  --新增客户率
                          loss_cnt,   --流失客户数
                          loss_waybill_sum,  --流失客户运单总量
                          loss_customer_rate,  --流失率
                          retrieve_cnt,  --挽回客户数
                          retrieve_waybill_sum,   --挽回客户运单总量
                          retrieve_customer_rate       --挽回率
 from fran_customer
 union all
 select
                         org_level,
                          date_type,
                          customer_level,           --客户等级
                          ordersource_code,         --客户平台ID
                          ordersource_name,  --平台名字
                          subordinate_agent_code,
                          subordinate_agent_name,
                          first_franchisee_code,
                          first_franchisee_name,
                          customer_belong_network_id,  --客户所属网点ID
                          customer_belong_network_name,  --客户所属网点名字
                          customer_cnt,    --发件客数
                          today_customer_cnt,
                          customer_cnt_rate,
                         last_month_customer_cnt,
                          month_customer_waybill_sum,--发件客户运单总量
                          today_month_customer_waybill_sum,
                          customer_waybill_rate,
                          customer_up_rate,  --客户提升率
                          new_cnt,      --新增客户数
                          new_customer_waybill_sum,  --新增客户运单总量
                          new_customer_rate,  --新增客户率
                          loss_cnt,   --流失客户数
                          loss_waybill_sum,  --流失客户运单总量
                          loss_customer_rate,  --流失率
                          retrieve_cnt,  --挽回客户数
                          retrieve_waybill_sum,   --挽回客户运单总量
                          retrieve_customer_rate       --挽回率
 from fran_customer_sum
     union all
 select
 org_level,
              date_type,
              customer_level,           --客户等级
              ordersource_code,         --客户平台ID
              ordersource_name,  --平台名字
              subordinate_agent_code,
              subordinate_agent_name,
              first_franchisee_code,
              first_franchisee_name,
              customer_belong_network_id,  --客户所属网点ID
              customer_belong_network_name,  --客户所属网点名字
              customer_cnt,    --发件客数
              today_customer_cnt,
              customer_cnt_rate,
              last_month_customer_cnt,
              month_customer_waybill_sum,--发件客户运单总量
              today_month_customer_waybill_sum,
              customer_waybill_rate,
              customer_up_rate,  --客户提升率
              new_cnt,      --新增客户数
              new_customer_waybill_sum,  --新增客户运单总量
              new_customer_rate,  --新增客户率
              loss_cnt,   --流失客户数
              loss_waybill_sum,  --流失客户运单总量
              loss_customer_rate,  --流失率
              retrieve_cnt,  --挽回客户数
              retrieve_waybill_sum,   --挽回客户运单总量
              retrieve_customer_rate       --挽回率
 from agent_customer
 union all
 select
 org_level,
              date_type,
              customer_level,           --客户等级
              ordersource_code,         --客户平台ID
              ordersource_name,  --平台名字
              subordinate_agent_code,
              subordinate_agent_name,
              first_franchisee_code,
              first_franchisee_name,
              customer_belong_network_id,  --客户所属网点ID
              customer_belong_network_name,  --客户所属网点名字
              customer_cnt,    --发件客数
              today_customer_cnt,
              customer_cnt_rate,
              last_month_customer_cnt,
              month_customer_waybill_sum,--发件客户运单总量
              today_month_customer_waybill_sum,
              customer_waybill_rate,
              customer_up_rate,  --客户提升率
              new_cnt,      --新增客户数
              new_customer_waybill_sum,  --新增客户运单总量
              new_customer_rate,  --新增客户率
              loss_cnt,   --流失客户数
              loss_waybill_sum,  --流失客户运单总量
              loss_customer_rate,  --流失率
              retrieve_cnt,  --挽回客户数
              retrieve_waybill_sum,   --挽回客户运单总量
              retrieve_customer_rate       --挽回率
 from agent_customer_sum
 ) a
 distribute by 10;